﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace Webchatting.Dal
{
    public class OperationGroup
    {
         #region 变量
        public static readonly string connStr = (ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
        public SqlConnection ConnSql
        {
            set;
            get;
        }
        #endregion

        /// <summary>
        /// 构造函数，用于初始化 
        /// </summary>
        public OperationGroup()
        {
            //初始化数据库连接
            this.ConnSql = new SqlConnection(connStr);
        }

        //创建群
        public int CreateGroup(string GId,string RUId,string GroupName,string Groupdes)
        {
            SqlParameter[] sqlParams = new SqlParameter[4];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);
            sqlParams[2] = new SqlParameter("@GroupName", GroupName);
            sqlParams[3] = new SqlParameter("@GroupDes",Groupdes);
            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spCreateGroup", sqlParams);
            return 1;
        }
        

        //获得群信息
        public IList<string> GetGroupDetails(string GId) 
        {
            IList<string> ilist = new List<string>();
            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GId", GId);
            SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT * FROM GroupList WHERE GroupId='" + GId+"'");
            ilist.Add(dr["GroupId"].ToString().Trim());
            ilist.Add(dr["RUId"].ToString().Trim());
            ilist.Add(dr["GroupName"].ToString().Trim());
            ilist.Add(dr["GrouDes"].ToString().Trim());
            return ilist;
        }

        
        
        //查找群 加入群时候用
         public string SearchGroup(string serachId, ArrayList arrGroupIds, ArrayList arrGroupNames, ArrayList arrGroupOwners, ArrayList arrGroupDess)
         {
             arrGroupIds.Clear();
             arrGroupNames.Clear();
             arrGroupOwners.Clear();
             arrGroupDess.Clear();
             StringBuilder sb = new StringBuilder();
             sb.Append("<table border='0px' style='width:100%;border:1px solid #CCCCCC;' cellPadding='0' cellSpacing='2'>");
             sb.Append("<tr><td style='height:25px;background:#CCCCCC'>群ID</td><td style='height:25px;background:#CCCCCC'>群名称</td><td style='height:25px;background:#CCCCCC'>创建者</td><td style='height:25px;background:#CCCCCC'>群描述</td></tr>");
             SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT [GroupId],[RUId],[GroupName],[GroupDes] FROM [GroupList] WHERE CONVERT(nchar(20),[GroupId]) LIKE" + "'%" + serachId + "%'");


             while (dr.Read())
             {
                 Encoding edUTF = Encoding.UTF8;
                 Encoding edGB = Encoding.GetEncoding("GB2312");
                 sb.Append("<tr>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupId"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupName"].ToString() + "</td>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["RUId"].ToString() + "</td>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupDes"].ToString() + "</tr>");
                 arrGroupIds.Add(dr["GroupId"].ToString().Trim());
                 arrGroupNames.Add(dr["GroupName"].ToString().Trim());
                 arrGroupOwners.Add(dr["RUId"].ToString().Trim());
                 arrGroupDess.Add(dr["GroupDes"].ToString().Trim());
             }
             sb.Append("</table>");
             dr.Close();
             ConnSql.Close();
             return sb.ToString();
         }
        
        
        //群加载  退出群操作时使用 
         public string LoadGroup(string RUId, ArrayList arrGroupIds, ArrayList arrGroupIdstemp, ArrayList arrGroupNames, ArrayList arrGroupOwners, ArrayList arrGroupDess)
         {
             
             arrGroupIds.Clear();
             arrGroupNames.Clear();
             arrGroupOwners.Clear();
             arrGroupDess.Clear();
             arrGroupIdstemp.Clear();
             StringBuilder sb = new StringBuilder();
             sb.Append("<table border='0px' style='width:100%;border:1px solid #CCCCCC;' cellPadding='0' cellSpacing='2'>");
             sb.Append("<tr><td style='height:25px;background:#CCCCCC'>群ID</td><td style='height:25px;background:#CCCCCC'>群名称</td><td style='height:25px;background:#CCCCCC'>创建者</td><td style='height:25px;background:#CCCCCC'>群描述</td></tr>");
             SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT GroupList.[GroupId],GroupName,GroupDes, GroupList.[RUId] FROM [GroupMem],GroupList WHERE  [GroupMem].GroupId=GroupList.GroupId and [GroupMem].RUId ='" + RUId + "'");
           
            
             while (dr.Read())
             {
                 
                 Encoding edUTF = Encoding.UTF8;
                 Encoding edGB = Encoding.GetEncoding("GB2312");
                 sb.Append("<tr>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupId"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupName"].ToString() + "</td>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["RUId"].ToString() + "</td>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupDes"].ToString() + "</td>" + "</tr>");
                 arrGroupIds.Add(dr["GroupId"].ToString().Trim());
                 arrGroupOwners.Add(dr["RUId"].ToString().Trim());
                 arrGroupDess.Add(dr["GroupDes"].ToString().Trim());
                 arrGroupNames.Add(dr["GroupName"].ToString().Trim());
                 
             }
            
             
             sb.Append("</table>");
             dr.Close();
             ConnSql.Close();
             return sb.ToString();
         }
       
       
        //加载我创建的群  添加成员 删除成员 修改群信息时用
         public string LoadMyGroupDetails(string RUId, ArrayList arrGroupIds, ArrayList arrGroupNames,  ArrayList arrGroupDess)
         {
             arrGroupIds.Clear();
             arrGroupNames.Clear(); 
             arrGroupDess.Clear();
             StringBuilder sb = new StringBuilder();
             sb.Append("<table border='0px' style='width:100%;border:1px solid #CCCCCC;' cellPadding='0' cellSpacing='2'>");
             sb.Append("<tr><td style='height:25px;background:#CCCCCC'>群ID</td><td style='height:25px;background:#CCCCCC'>群名称</td><td style='height:25px;background:#CCCCCC'>群描述</td></tr>");
             SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT [GroupId],[GroupName],[GroupDes] FROM [GroupList] WHERE [RUId]='" + RUId+"'");


             while (dr.Read())
             {
                 Encoding edUTF = Encoding.UTF8;
                 Encoding edGB = Encoding.GetEncoding("GB2312");
                 sb.Append("<tr>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupId"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupName"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupDes"].ToString() + "</tr>");
                 arrGroupIds.Add(dr["GroupId"].ToString().Trim());
                 arrGroupNames.Add(dr["GroupName"].ToString().Trim());
                 arrGroupDess.Add(dr["GroupDes"].ToString().Trim());
             }
             sb.Append("</table>");
             dr.Close();
             ConnSql.Close();
             return sb.ToString();
         }
        //改群信息时用
         public string LoadGroupDetail(string RUId, ArrayList arrGroupIds, ArrayList arrGroupNames,ArrayList arrGroupDess)
         {
             arrGroupIds.Clear();
             arrGroupNames.Clear();
            
             arrGroupDess.Clear();
             StringBuilder sb = new StringBuilder();
             sb.Append("<table border='0px' style='width:100%;border:1px solid #CCCCCC;' cellPadding='0' cellSpacing='2'>");
             sb.Append("<tr><td style='height:25px;background:#CCCCCC'>群ID</td><td style='height:25px;background:#CCCCCC'>群名称</td><td style='height:25px;background:#CCCCCC'>群描述</td></tr>");
             SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT [GroupId],[GroupName],[GroupDes] FROM [GroupList] WHERE RUId = '"+ RUId+"'");


             while (dr.Read())
             {
                 Encoding edUTF = Encoding.UTF8;
                 Encoding edGB = Encoding.GetEncoding("GB2312");
                 sb.Append("<tr>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupId"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupName"].ToString() + "</td>"  + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GroupDes"].ToString() + "</tr>");
                 arrGroupIds.Add(dr["GroupId"].ToString().Trim());
                 arrGroupNames.Add(dr["GroupName"].ToString().Trim());
                 arrGroupDess.Add(dr["GroupDes"].ToString().Trim());
             }
             sb.Append("</table>");
             dr.Close();
             ConnSql.Close();
             return sb.ToString();
         }
     
        //修改群信息
        public int ModifyGroupDetails(string RUId,string GId,string GroupName,string Groupdes)
        {
            SqlParameter[] sqlParams = new SqlParameter[4];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);
            sqlParams[2] = new SqlParameter("@GroupName", GroupName);
            sqlParams[3] = new SqlParameter("@GroupDes",Groupdes);

            return SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spModifyGroupDetails", sqlParams);
        }
        

       
        //加入群
        public int AddGroupMum(string GId,string RUId,string GMRemarkName)
        {

            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);
            sqlParams[2] = new SqlParameter("@GMRemarkName", GMRemarkName);
            
            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spAddGroupMum", sqlParams);
            return 1;
        }
       
        //获取创建者
        public string GetGroupOwner(string GId) 
        {
            StringBuilder sb = new StringBuilder();
            SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT [RUId] FROM ChattingDB.dbo.GroupList WHERE GroupId = '" + GId + "'");
            while (dr.Read())
            {
                sb.Append(dr["RUId"].ToString().Trim());
            }
            dr.Close();
            ConnSql.Close();

            return sb.ToString();
        }

        public int ExitGroup(string GId, string RUId)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);
            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spExitGroup", sqlParams);
            return 1;
        }

        //获取群名品啊
        public string  GetGRemarkName(string GId, string RUId)
        {
            StringBuilder sb = new StringBuilder();
            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);


            SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT [GMRemarkName] FROM ChattingDB.dbo.GroupMem WHERE GroupId = '" + GId + "'AND RUId = '" + RUId + "' ");
            while (dr.Read())
            {
                sb.Append(dr["GMRemarkName"].ToString().Trim());
            }
            dr.Close();
            return sb.ToString();
        }
        //修改群名片
        public int ModifyGRemarkName(string GId,string RUId,string GMRemarkName)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);
            sqlParams[2] = new SqlParameter("@GMRemarkName", GMRemarkName);

            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spModifyGroupMumRemarkName", sqlParams);
            return 1;
        }

        //加载群成员  删除群成员时用
        public string LoadGroupMum(string GroupId, ArrayList arrGroupMemIds, ArrayList arrGroupMemRemarkNames)
        {
            arrGroupMemIds.Clear();
            arrGroupMemRemarkNames.Clear();

            
            StringBuilder sb = new StringBuilder();
            sb.Append("<table border='0px' style='width:100%;border:1px solid #CCCCCC;' cellPadding='0' cellSpacing='2'>");
            sb.Append("<tr><td style='height:25px;background:#CCCCCC'>群成员ID</td><td style='height:25px;background:#CCCCCC'>群成员名片</td></tr>");
            SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
               "SELECT [RUId],[GMRemarkName] FROM [GroupMem] WHERE [GroupId]='" + GroupId+"'");


            while (dr.Read())
            {
                Encoding edUTF = Encoding.UTF8;
                Encoding edGB = Encoding.GetEncoding("GB2312");
                sb.Append("<tr>" + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["RUId"].ToString() + "<td style='height:25px;border-bottom:1px dashed #CCCCCC;'>" + dr["GMRemarkName"].ToString() + "</td>" + "</tr>");
                arrGroupMemIds.Add(dr["RUId"].ToString().Trim());
                arrGroupMemRemarkNames.Add(dr["GMRemarkName"].ToString().Trim());
                
            }
            sb.Append("</table>");
            dr.Close();
            ConnSql.Close();
            return sb.ToString();
        }

         //加载群成员 进入群后使用
        public string GetGroupMumList(string GId)
        {
            
            StringBuilder sb = new StringBuilder();
            SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
               "SELECT [RUId],[GMRemarkName] FROM ChattingDB.dbo.GroupMem WHERE  [GroupId]='" + GId + "'");
            sb.Append("<ul style='width:120px;margin:0;padding:0;' id='groupMem'>"+"群成员"+"</br>");
            while (dr.Read())
            {

                Encoding edUTF = Encoding.UTF8;
                Encoding edGB = Encoding.GetEncoding("GB2312");
               
                sb.Append("<li id='li" + dr["RUId"].ToString().Trim() +
                    "' onmouseover='this.style.backgroundColor=\"#F3FBFE\"' onmouseout='this.style.backgroundColor=\"#d6dff7\"'>"+
                    dr["GMRemarkName"].ToString() + "(" +
                    dr["RUId"].ToString() +")"+"</li>");
               
                
            }
            sb.Append("</ul>");
            dr.Close();
            return sb.ToString();
        }


        //踢出群成员
        public int DeleteGroupMum(string GId,string RUId)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            sqlParams[1] = new SqlParameter("@RUId", RUId);

            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spDeleteGroupMum", sqlParams);
            return 1;
            
        }

        public int DeleteGroupMumAll(string GId)
        {
             SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GroupId", GId);
            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spDeleteGroupMumAll", sqlParams);
            return 1;
        }

        //解散群
        public int DropGroup(string GId) 
        {
            SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@GroupId", GId) };
            SqlHelper.ExecuteNonQuery(ConnSql, CommandType.StoredProcedure, "spDropGroup", sqlParams);
            return 1;
        }



        //获取用户群列表 聊天前加载群使用
        public string GetGroupList(string RUId) 
        {


             StringBuilder sb = new StringBuilder();
             SqlDataReader dr = SqlHelper.ExecuteReader(ConnSql, CommandType.Text,
                "SELECT GroupList.[GroupId],GroupName,GroupDes, GroupList.[RUId] FROM [GroupMem],GroupList WHERE  [GroupMem].GroupId=GroupList.GroupId and [GroupMem].RUId ='" + RUId + "'");
             sb.Append("<ul>");
             while (dr.Read())
             {
                 Encoding edUTF = Encoding.UTF8;
                 Encoding edGB = Encoding.GetEncoding("GB2312");
                 
                 sb.Append("<li id='li"  + dr["GroupId"].ToString().Trim() +
                     "' onmouseover='this.style.backgroundColor=\"#F3FBFE\"' onmouseout='this.style.backgroundColor=\"#d6dff7\"'><span style='width:100%;cursor:pointer;' id='a"
                    + dr["GroupId"].ToString().Trim() +
                    "' onclick=\"openn(" + dr["GroupId"].ToString() +
                    ",'" + edGB.GetString(Encoding.Convert(edUTF, edGB, edUTF.GetBytes(dr["GroupName"].ToString().Trim()))) +
                    "');\">" + dr["GroupName"].ToString() + "(" +
                    dr["GroupId"].ToString() +")"+"["+
                    dr["GroupDes"].ToString() +"]"+
                    "</span></li>");

                

             }
             sb.Append("</ul>");
             dr.Close();
             ConnSql.Close();
             return sb.ToString();
           
          
        }
    }
}
